/*
 Navicat Premium Data Transfer

 Source Server         : 127.0.0.1
 Source Server Type    : SQL Server
 Source Server Version : 10501600
 Source Host           : 127.0.0.1:1433
 Source Catalog        : DMJJXT
 Source Schema         : dbo

 Target Server Type    : SQL Server
 Target Server Version : 10501600
 File Encoding         : 65001

 Date: 01/05/2019 10:18:58
*/


-- ----------------------------
-- Table structure for ad_booth
-- ----------------------------
IF EXISTS (SELECT * FROM sys.all_objects WHERE object_id = OBJECT_ID(N'[dbo].[ad_booth]') AND type IN ('U'))
	DROP TABLE [dbo].[ad_booth]
GO

CREATE TABLE [dbo].[ad_booth] (
  [Id] int  IDENTITY(1,1) NOT NULL,
  [Store] varchar(50) COLLATE Chinese_PRC_CI_AS  NULL,
  [Floor] varchar(50) COLLATE Chinese_PRC_CI_AS  NULL,
  [BoothNo] varchar(50) COLLATE Chinese_PRC_CI_AS  NULL,
  [RentalArea] decimal(18,2)  NULL,
  [CreateTime] datetime  NULL,
  [CreateManName] varchar(50) COLLATE Chinese_PRC_CI_AS  NULL,
  [CreateManNo] varchar(50) COLLATE Chinese_PRC_CI_AS  NULL,
  [EditTime] datetime  NULL,
  [EditManName] varchar(50) COLLATE Chinese_PRC_CI_AS  NULL,
  [EditManNo] varchar(50) COLLATE Chinese_PRC_CI_AS  NULL
)
GO

ALTER TABLE [dbo].[ad_booth] SET (LOCK_ESCALATION = TABLE)
GO

EXEC sp_addextendedproperty
'MS_Description', N'店面',
'SCHEMA', N'dbo',
'TABLE', N'ad_booth',
'COLUMN', N'Store'
GO

EXEC sp_addextendedproperty
'MS_Description', N'楼层',
'SCHEMA', N'dbo',
'TABLE', N'ad_booth',
'COLUMN', N'Floor'
GO

EXEC sp_addextendedproperty
'MS_Description', N'摊位编号',
'SCHEMA', N'dbo',
'TABLE', N'ad_booth',
'COLUMN', N'BoothNo'
GO

EXEC sp_addextendedproperty
'MS_Description', N'租赁面积',
'SCHEMA', N'dbo',
'TABLE', N'ad_booth',
'COLUMN', N'RentalArea'
GO

EXEC sp_addextendedproperty
'MS_Description', N'创建时间',
'SCHEMA', N'dbo',
'TABLE', N'ad_booth',
'COLUMN', N'CreateTime'
GO

EXEC sp_addextendedproperty
'MS_Description', N'创建人姓名',
'SCHEMA', N'dbo',
'TABLE', N'ad_booth',
'COLUMN', N'CreateManName'
GO

EXEC sp_addextendedproperty
'MS_Description', N'创建人员工编号',
'SCHEMA', N'dbo',
'TABLE', N'ad_booth',
'COLUMN', N'CreateManNo'
GO

EXEC sp_addextendedproperty
'MS_Description', N'编辑时间',
'SCHEMA', N'dbo',
'TABLE', N'ad_booth',
'COLUMN', N'EditTime'
GO

EXEC sp_addextendedproperty
'MS_Description', N'编辑人姓名',
'SCHEMA', N'dbo',
'TABLE', N'ad_booth',
'COLUMN', N'EditManName'
GO

EXEC sp_addextendedproperty
'MS_Description', N'编辑人员工编号',
'SCHEMA', N'dbo',
'TABLE', N'ad_booth',
'COLUMN', N'EditManNo'
GO

EXEC sp_addextendedproperty
'MS_Description', N'摊位信息表',
'SCHEMA', N'dbo',
'TABLE', N'ad_booth'
GO


-- ----------------------------
-- Table structure for ad_brand
-- ----------------------------
IF EXISTS (SELECT * FROM sys.all_objects WHERE object_id = OBJECT_ID(N'[dbo].[ad_brand]') AND type IN ('U'))
	DROP TABLE [dbo].[ad_brand]
GO

CREATE TABLE [dbo].[ad_brand] (
  [Id] int  IDENTITY(1,1) NOT NULL,
  [BrandName] varchar(255) COLLATE Chinese_PRC_CI_AS  NULL,
  [BrandProvince] varchar(255) COLLATE Chinese_PRC_CI_AS  NULL,
  [BrandCity] varchar(255) COLLATE Chinese_PRC_CI_AS  NULL,
  [BrandCounty] varchar(255) COLLATE Chinese_PRC_CI_AS  NULL,
  [BrandAddress] varchar(255) COLLATE Chinese_PRC_CI_AS  NULL,
  [LegalPhone] varchar(255) COLLATE Chinese_PRC_CI_AS  NULL,
  [BusinessName] varchar(255) COLLATE Chinese_PRC_CI_AS  NULL,
  [BusinessJob] varchar(255) COLLATE Chinese_PRC_CI_AS  NULL,
  [BusinessPhone] varchar(255) COLLATE Chinese_PRC_CI_AS  NULL,
  [CreateFactoryDate] date  NULL,
  [FactoryArea] decimal(18,2)  NULL,
  [FactoryYearValue] decimal(18,2)  NULL,
  [AllStoreNumber] int  NULL,
  [FactoryRegisteredMoney] decimal(18,2)  NULL,
  [ChinaJxJob] varchar(255) COLLATE Chinese_PRC_CI_AS  NULL,
  [FactorySlogan] varchar(255) COLLATE Chinese_PRC_CI_AS  NULL,
  [StarLan] varchar(255) COLLATE Chinese_PRC_CI_AS DEFAULT '' NULL,
  [CentralWord] varchar(255) COLLATE Chinese_PRC_CI_AS  NULL,
  [OnlineShop] varchar(255) COLLATE Chinese_PRC_CI_AS  NULL,
  [EnterpriseWebsite] varchar(255) COLLATE Chinese_PRC_CI_AS  NULL,
  [WxNo] varchar(255) COLLATE Chinese_PRC_CI_AS  NULL,
  [CreateTime] datetime  NULL,
  [CreateManName] varchar(255) COLLATE Chinese_PRC_CI_AS  NULL,
  [CreateManNo] varchar(255) COLLATE Chinese_PRC_CI_AS  NULL,
  [EditTime] datetime  NULL,
  [EditManName] varchar(255) COLLATE Chinese_PRC_CI_AS  NULL,
  [EditManNo] varchar(255) COLLATE Chinese_PRC_CI_AS  NULL,
  [BrandNo] varchar(255) COLLATE Chinese_PRC_CI_AS  NOT NULL,
  [CityBrandStore] varchar(255) COLLATE Chinese_PRC_CI_AS  NULL,
  [ZlProduct] varchar(255) COLLATE Chinese_PRC_CI_AS  NULL,
  [DelStatus] int DEFAULT ((0)) NULL
)
GO

ALTER TABLE [dbo].[ad_brand] SET (LOCK_ESCALATION = TABLE)
GO

EXEC sp_addextendedproperty
'MS_Description', N'品牌名称',
'SCHEMA', N'dbo',
'TABLE', N'ad_brand',
'COLUMN', N'BrandName'
GO

EXEC sp_addextendedproperty
'MS_Description', N'所在省',
'SCHEMA', N'dbo',
'TABLE', N'ad_brand',
'COLUMN', N'BrandProvince'
GO

EXEC sp_addextendedproperty
'MS_Description', N'所在市',
'SCHEMA', N'dbo',
'TABLE', N'ad_brand',
'COLUMN', N'BrandCity'
GO

EXEC sp_addextendedproperty
'MS_Description', N'所在区/县',
'SCHEMA', N'dbo',
'TABLE', N'ad_brand',
'COLUMN', N'BrandCounty'
GO

EXEC sp_addextendedproperty
'MS_Description', N'品牌详细地址
   输入格式： 省 市 县 详细地址',
'SCHEMA', N'dbo',
'TABLE', N'ad_brand',
'COLUMN', N'BrandAddress'
GO

EXEC sp_addextendedproperty
'MS_Description', N'厂家法人联系电话',
'SCHEMA', N'dbo',
'TABLE', N'ad_brand',
'COLUMN', N'LegalPhone'
GO

EXEC sp_addextendedproperty
'MS_Description', N'厂家业务联系人姓名',
'SCHEMA', N'dbo',
'TABLE', N'ad_brand',
'COLUMN', N'BusinessName'
GO

EXEC sp_addextendedproperty
'MS_Description', N'厂家业务联系人职务',
'SCHEMA', N'dbo',
'TABLE', N'ad_brand',
'COLUMN', N'BusinessJob'
GO

EXEC sp_addextendedproperty
'MS_Description', N'厂家业务联系人电话',
'SCHEMA', N'dbo',
'TABLE', N'ad_brand',
'COLUMN', N'BusinessPhone'
GO

EXEC sp_addextendedproperty
'MS_Description', N'建厂时间',
'SCHEMA', N'dbo',
'TABLE', N'ad_brand',
'COLUMN', N'CreateFactoryDate'
GO

EXEC sp_addextendedproperty
'MS_Description', N'厂家占地规模',
'SCHEMA', N'dbo',
'TABLE', N'ad_brand',
'COLUMN', N'FactoryArea'
GO

EXEC sp_addextendedproperty
'MS_Description', N'厂家年产值',
'SCHEMA', N'dbo',
'TABLE', N'ad_brand',
'COLUMN', N'FactoryYearValue'
GO

EXEC sp_addextendedproperty
'MS_Description', N'全国店铺数量',
'SCHEMA', N'dbo',
'TABLE', N'ad_brand',
'COLUMN', N'AllStoreNumber'
GO

EXEC sp_addextendedproperty
'MS_Description', N'厂家注册资金',
'SCHEMA', N'dbo',
'TABLE', N'ad_brand',
'COLUMN', N'FactoryRegisteredMoney'
GO

EXEC sp_addextendedproperty
'MS_Description', N'中国家协任职：
   副理事长单位
   理事单位
   无任职
   ',
'SCHEMA', N'dbo',
'TABLE', N'ad_brand',
'COLUMN', N'ChinaJxJob'
GO

EXEC sp_addextendedproperty
'MS_Description', N'厂家广告语',
'SCHEMA', N'dbo',
'TABLE', N'ad_brand',
'COLUMN', N'FactorySlogan'
GO

EXEC sp_addextendedproperty
'MS_Description', N'明星代言 0 无 1有',
'SCHEMA', N'dbo',
'TABLE', N'ad_brand',
'COLUMN', N'StarLan'
GO

EXEC sp_addextendedproperty
'MS_Description', N'央字广告:
   央视 央广 无',
'SCHEMA', N'dbo',
'TABLE', N'ad_brand',
'COLUMN', N'CentralWord'
GO

EXEC sp_addextendedproperty
'MS_Description', N'线上店铺
   天猫 京东 淘宝 自有 无',
'SCHEMA', N'dbo',
'TABLE', N'ad_brand',
'COLUMN', N'OnlineShop'
GO

EXEC sp_addextendedproperty
'MS_Description', N'企业网站',
'SCHEMA', N'dbo',
'TABLE', N'ad_brand',
'COLUMN', N'EnterpriseWebsite'
GO

EXEC sp_addextendedproperty
'MS_Description', N'微信公众号',
'SCHEMA', N'dbo',
'TABLE', N'ad_brand',
'COLUMN', N'WxNo'
GO

EXEC sp_addextendedproperty
'MS_Description', N'创建时间',
'SCHEMA', N'dbo',
'TABLE', N'ad_brand',
'COLUMN', N'CreateTime'
GO

EXEC sp_addextendedproperty
'MS_Description', N'创建人姓名',
'SCHEMA', N'dbo',
'TABLE', N'ad_brand',
'COLUMN', N'CreateManName'
GO

EXEC sp_addextendedproperty
'MS_Description', N'创建人员工编号',
'SCHEMA', N'dbo',
'TABLE', N'ad_brand',
'COLUMN', N'CreateManNo'
GO

EXEC sp_addextendedproperty
'MS_Description', N'编辑时间',
'SCHEMA', N'dbo',
'TABLE', N'ad_brand',
'COLUMN', N'EditTime'
GO

EXEC sp_addextendedproperty
'MS_Description', N'编辑人姓名',
'SCHEMA', N'dbo',
'TABLE', N'ad_brand',
'COLUMN', N'EditManName'
GO

EXEC sp_addextendedproperty
'MS_Description', N'编辑人员工编号',
'SCHEMA', N'dbo',
'TABLE', N'ad_brand',
'COLUMN', N'EditManNo'
GO

EXEC sp_addextendedproperty
'MS_Description', N'品牌编号',
'SCHEMA', N'dbo',
'TABLE', N'ad_brand',
'COLUMN', N'BrandNo'
GO

EXEC sp_addextendedproperty
'MS_Description', N'本市同行业卖场',
'SCHEMA', N'dbo',
'TABLE', N'ad_brand',
'COLUMN', N'CityBrandStore'
GO

EXEC sp_addextendedproperty
'MS_Description', N'专利产品 0：无 1：有',
'SCHEMA', N'dbo',
'TABLE', N'ad_brand',
'COLUMN', N'ZlProduct'
GO

EXEC sp_addextendedproperty
'MS_Description', N'删除标志 0未删除 1已删除',
'SCHEMA', N'dbo',
'TABLE', N'ad_brand',
'COLUMN', N'DelStatus'
GO

EXEC sp_addextendedproperty
'MS_Description', N'品牌信息表',
'SCHEMA', N'dbo',
'TABLE', N'ad_brand'
GO


-- ----------------------------
-- Table structure for ad_brand_pic
-- ----------------------------
IF EXISTS (SELECT * FROM sys.all_objects WHERE object_id = OBJECT_ID(N'[dbo].[ad_brand_pic]') AND type IN ('U'))
	DROP TABLE [dbo].[ad_brand_pic]
GO

CREATE TABLE [dbo].[ad_brand_pic] (
  [Id] int  IDENTITY(1,1) NOT NULL,
  [BrandNo] varchar(200) COLLATE Chinese_PRC_CI_AS  NOT NULL,
  [PicType] int  NULL,
  [PicUrl] varchar(200) COLLATE Chinese_PRC_CI_AS  NULL,
  [CreateTime] datetime  NULL,
  [CreateManName] varchar(50) COLLATE Chinese_PRC_CI_AS  NULL,
  [CreateManNo] varchar(50) COLLATE Chinese_PRC_CI_AS  NULL,
  [EditTime] datetime  NULL,
  [EditManName] varchar(50) COLLATE Chinese_PRC_CI_AS  NULL,
  [EditManNo] varchar(50) COLLATE Chinese_PRC_CI_AS  NULL
)
GO

ALTER TABLE [dbo].[ad_brand_pic] SET (LOCK_ESCALATION = TABLE)
GO

EXEC sp_addextendedproperty
'MS_Description', N'主键',
'SCHEMA', N'dbo',
'TABLE', N'ad_brand_pic',
'COLUMN', N'Id'
GO

EXEC sp_addextendedproperty
'MS_Description', N'品牌编号',
'SCHEMA', N'dbo',
'TABLE', N'ad_brand_pic',
'COLUMN', N'BrandNo'
GO

EXEC sp_addextendedproperty
'MS_Description', N'类型 1:厂家电子LOGO（图片）FacrotyLogo
       2:商户代理许可证 Shdlxkz
       3:进口商品证明 Jkspzm
       4:木材证明 Mczm
       5:厂家产品系列电子图纸（图片）Cjcpxldztz
       6:厂家产品质检报告（图片） Cjcpzjbg
       7:厂家营业执照复印件（图片） Cjyjzzfyj
       8:厂家法人身份证复印件（图片） Cjfrsfzfyj',
'SCHEMA', N'dbo',
'TABLE', N'ad_brand_pic',
'COLUMN', N'PicType'
GO

EXEC sp_addextendedproperty
'MS_Description', N'图片url',
'SCHEMA', N'dbo',
'TABLE', N'ad_brand_pic',
'COLUMN', N'PicUrl'
GO

EXEC sp_addextendedproperty
'MS_Description', N'创建时间',
'SCHEMA', N'dbo',
'TABLE', N'ad_brand_pic',
'COLUMN', N'CreateTime'
GO

EXEC sp_addextendedproperty
'MS_Description', N'创建人姓名',
'SCHEMA', N'dbo',
'TABLE', N'ad_brand_pic',
'COLUMN', N'CreateManName'
GO

EXEC sp_addextendedproperty
'MS_Description', N'创建人员工编号',
'SCHEMA', N'dbo',
'TABLE', N'ad_brand_pic',
'COLUMN', N'CreateManNo'
GO

EXEC sp_addextendedproperty
'MS_Description', N'编辑时间',
'SCHEMA', N'dbo',
'TABLE', N'ad_brand_pic',
'COLUMN', N'EditTime'
GO

EXEC sp_addextendedproperty
'MS_Description', N'编辑人姓名',
'SCHEMA', N'dbo',
'TABLE', N'ad_brand_pic',
'COLUMN', N'EditManName'
GO

EXEC sp_addextendedproperty
'MS_Description', N'编辑人员工编号',
'SCHEMA', N'dbo',
'TABLE', N'ad_brand_pic',
'COLUMN', N'EditManNo'
GO

EXEC sp_addextendedproperty
'MS_Description', N'品牌图片表',
'SCHEMA', N'dbo',
'TABLE', N'ad_brand_pic'
GO


-- ----------------------------
-- Table structure for ad_category_style
-- ----------------------------
IF EXISTS (SELECT * FROM sys.all_objects WHERE object_id = OBJECT_ID(N'[dbo].[ad_category_style]') AND type IN ('U'))
	DROP TABLE [dbo].[ad_category_style]
GO

CREATE TABLE [dbo].[ad_category_style] (
  [Id] int  IDENTITY(1,1) NOT NULL,
  [ParentId] int  NULL,
  [Name] varchar(50) COLLATE Chinese_PRC_CI_AS  NULL
)
GO

ALTER TABLE [dbo].[ad_category_style] SET (LOCK_ESCALATION = TABLE)
GO

EXEC sp_addextendedproperty
'MS_Description', N'父Id',
'SCHEMA', N'dbo',
'TABLE', N'ad_category_style',
'COLUMN', N'ParentId'
GO

EXEC sp_addextendedproperty
'MS_Description', N'名称',
'SCHEMA', N'dbo',
'TABLE', N'ad_category_style',
'COLUMN', N'Name'
GO

EXEC sp_addextendedproperty
'MS_Description', N'产品品类-风格表',
'SCHEMA', N'dbo',
'TABLE', N'ad_category_style'
GO


-- ----------------------------
-- Table structure for ad_contract
-- ----------------------------
IF EXISTS (SELECT * FROM sys.all_objects WHERE object_id = OBJECT_ID(N'[dbo].[ad_contract]') AND type IN ('U'))
	DROP TABLE [dbo].[ad_contract]
GO

CREATE TABLE [dbo].[ad_contract] (
  [Id] int  IDENTITY(1,1) NOT NULL,
  [ContractNo] varchar(50) COLLATE Chinese_PRC_CI_AS  NOT NULL,
  [Store] varchar(50) COLLATE Chinese_PRC_CI_AS  NULL,
  [Floor] varchar(50) COLLATE Chinese_PRC_CI_AS  NULL,
  [BrandNo] varchar(50) COLLATE Chinese_PRC_CI_AS  NULL,
  [BusinessAttr] varchar(50) COLLATE Chinese_PRC_CI_AS  NULL,
  [BusinessCategory] varchar(50) COLLATE Chinese_PRC_CI_AS  NULL,
  [SeriesNo] varchar(50) COLLATE Chinese_PRC_CI_AS  NULL,
  [ShNo] varchar(50) COLLATE Chinese_PRC_CI_AS  NULL,
  [BoothNo] varchar(200) COLLATE Chinese_PRC_CI_AS  NULL,
  [GtRatio] decimal(18,2)  NULL,
  [RentalArea] decimal(18,2)  NULL,
  [RentalStartDate] date  NULL,
  [RentalEndDate] date  NULL,
  [RentalMoney] decimal(18,2)  NULL,
  [PropertyMoney] decimal(18,2)  NULL,
  [SettleBank] varchar(50) COLLATE Chinese_PRC_CI_AS  NULL,
  [OpenBankName] varchar(50) COLLATE Chinese_PRC_CI_AS  NULL,
  [OpenBankType] varchar(20) COLLATE Chinese_PRC_CI_AS  NULL,
  [SettleOpenBank] varchar(200) COLLATE Chinese_PRC_CI_AS  NULL,
  [SettleBankCardNo] varchar(20) COLLATE Chinese_PRC_CI_AS  NULL,
  [ContractMoney] decimal(18,2)  NULL,
  [QualityMoney] decimal(18,2)  NULL,
  [YearSafeMoney] decimal(18,2)  NULL,
  [YearQualityTestMoney] decimal(18,2)  NULL,
  [SalesPickUp] decimal(18,2)  NULL,
  [BrandProMoney] decimal(18,2)  NULL,
  [FranchiserStore] varchar(50) COLLATE Chinese_PRC_CI_AS  NULL,
  [FranchiserBm] varchar(50) COLLATE Chinese_PRC_CI_AS  NULL,
  [FranchiserUserNo] varchar(50) COLLATE Chinese_PRC_CI_AS  NULL,
  [FranchiserName] varchar(50) COLLATE Chinese_PRC_CI_AS  NULL,
  [CreateTime] datetime  NULL,
  [CreateManName] varchar(50) COLLATE Chinese_PRC_CI_AS  NULL,
  [CreateManNo] varchar(50) COLLATE Chinese_PRC_CI_AS  NULL,
  [EditTime] datetime  NULL,
  [EditManName] varchar(50) COLLATE Chinese_PRC_CI_AS  NULL,
  [EditManNo] varchar(50) COLLATE Chinese_PRC_CI_AS  NULL,
  [ContractUrl] varchar(500) COLLATE Chinese_PRC_CI_AS  NULL,
  [ZwNo] varchar(50) COLLATE Chinese_PRC_CI_AS  NULL,
  [SafeMoney] decimal(18,2)  NULL
)
GO

ALTER TABLE [dbo].[ad_contract] SET (LOCK_ESCALATION = TABLE)
GO

EXEC sp_addextendedproperty
'MS_Description', N'合同编号',
'SCHEMA', N'dbo',
'TABLE', N'ad_contract',
'COLUMN', N'ContractNo'
GO

EXEC sp_addextendedproperty
'MS_Description', N'店面',
'SCHEMA', N'dbo',
'TABLE', N'ad_contract',
'COLUMN', N'Store'
GO

EXEC sp_addextendedproperty
'MS_Description', N'楼层',
'SCHEMA', N'dbo',
'TABLE', N'ad_contract',
'COLUMN', N'Floor'
GO

EXEC sp_addextendedproperty
'MS_Description', N'品牌No',
'SCHEMA', N'dbo',
'TABLE', N'ad_contract',
'COLUMN', N'BrandNo'
GO

EXEC sp_addextendedproperty
'MS_Description', N'行业属性',
'SCHEMA', N'dbo',
'TABLE', N'ad_contract',
'COLUMN', N'BusinessAttr'
GO

EXEC sp_addextendedproperty
'MS_Description', N'经营品类',
'SCHEMA', N'dbo',
'TABLE', N'ad_contract',
'COLUMN', N'BusinessCategory'
GO

EXEC sp_addextendedproperty
'MS_Description', N'产品系列No',
'SCHEMA', N'dbo',
'TABLE', N'ad_contract',
'COLUMN', N'SeriesNo'
GO

EXEC sp_addextendedproperty
'MS_Description', N'商户编号',
'SCHEMA', N'dbo',
'TABLE', N'ad_contract',
'COLUMN', N'ShNo'
GO

EXEC sp_addextendedproperty
'MS_Description', N'摊位号',
'SCHEMA', N'dbo',
'TABLE', N'ad_contract',
'COLUMN', N'BoothNo'
GO

EXEC sp_addextendedproperty
'MS_Description', N'公摊比例',
'SCHEMA', N'dbo',
'TABLE', N'ad_contract',
'COLUMN', N'GtRatio'
GO

EXEC sp_addextendedproperty
'MS_Description', N'租金计费面积',
'SCHEMA', N'dbo',
'TABLE', N'ad_contract',
'COLUMN', N'RentalArea'
GO

EXEC sp_addextendedproperty
'MS_Description', N'租赁期限开始时间',
'SCHEMA', N'dbo',
'TABLE', N'ad_contract',
'COLUMN', N'RentalStartDate'
GO

EXEC sp_addextendedproperty
'MS_Description', N'租赁期限截止时间',
'SCHEMA', N'dbo',
'TABLE', N'ad_contract',
'COLUMN', N'RentalEndDate'
GO

EXEC sp_addextendedproperty
'MS_Description', N'租金价格',
'SCHEMA', N'dbo',
'TABLE', N'ad_contract',
'COLUMN', N'RentalMoney'
GO

EXEC sp_addextendedproperty
'MS_Description', N'物业费',
'SCHEMA', N'dbo',
'TABLE', N'ad_contract',
'COLUMN', N'PropertyMoney'
GO

EXEC sp_addextendedproperty
'MS_Description', N'结款银行',
'SCHEMA', N'dbo',
'TABLE', N'ad_contract',
'COLUMN', N'SettleBank'
GO

EXEC sp_addextendedproperty
'MS_Description', N'开户行名称',
'SCHEMA', N'dbo',
'TABLE', N'ad_contract',
'COLUMN', N'OpenBankName'
GO

EXEC sp_addextendedproperty
'MS_Description', N'开户行类型
   建行 工行 农行 中行 河行',
'SCHEMA', N'dbo',
'TABLE', N'ad_contract',
'COLUMN', N'OpenBankType'
GO

EXEC sp_addextendedproperty
'MS_Description', N'结款银行卡开户行',
'SCHEMA', N'dbo',
'TABLE', N'ad_contract',
'COLUMN', N'SettleOpenBank'
GO

EXEC sp_addextendedproperty
'MS_Description', N'银行卡号',
'SCHEMA', N'dbo',
'TABLE', N'ad_contract',
'COLUMN', N'SettleBankCardNo'
GO

EXEC sp_addextendedproperty
'MS_Description', N'合同定金',
'SCHEMA', N'dbo',
'TABLE', N'ad_contract',
'COLUMN', N'ContractMoney'
GO

EXEC sp_addextendedproperty
'MS_Description', N'商品质量保证金',
'SCHEMA', N'dbo',
'TABLE', N'ad_contract',
'COLUMN', N'QualityMoney'
GO

EXEC sp_addextendedproperty
'MS_Description', N'商品年度保险费',
'SCHEMA', N'dbo',
'TABLE', N'ad_contract',
'COLUMN', N'YearSafeMoney'
GO

EXEC sp_addextendedproperty
'MS_Description', N'商品年度质检费',
'SCHEMA', N'dbo',
'TABLE', N'ad_contract',
'COLUMN', N'YearQualityTestMoney'
GO

EXEC sp_addextendedproperty
'MS_Description', N'商品销售提点',
'SCHEMA', N'dbo',
'TABLE', N'ad_contract',
'COLUMN', N'SalesPickUp'
GO

EXEC sp_addextendedproperty
'MS_Description', N'品牌年度宣传费
   单位 平米/元',
'SCHEMA', N'dbo',
'TABLE', N'ad_contract',
'COLUMN', N'BrandProMoney'
GO

EXEC sp_addextendedproperty
'MS_Description', N'招商签约人店面',
'SCHEMA', N'dbo',
'TABLE', N'ad_contract',
'COLUMN', N'FranchiserStore'
GO

EXEC sp_addextendedproperty
'MS_Description', N'招商签约人部门',
'SCHEMA', N'dbo',
'TABLE', N'ad_contract',
'COLUMN', N'FranchiserBm'
GO

EXEC sp_addextendedproperty
'MS_Description', N'招商签约人员工编号',
'SCHEMA', N'dbo',
'TABLE', N'ad_contract',
'COLUMN', N'FranchiserUserNo'
GO

EXEC sp_addextendedproperty
'MS_Description', N'招商签约人姓名',
'SCHEMA', N'dbo',
'TABLE', N'ad_contract',
'COLUMN', N'FranchiserName'
GO

EXEC sp_addextendedproperty
'MS_Description', N'创建时间',
'SCHEMA', N'dbo',
'TABLE', N'ad_contract',
'COLUMN', N'CreateTime'
GO

EXEC sp_addextendedproperty
'MS_Description', N'创建人姓名',
'SCHEMA', N'dbo',
'TABLE', N'ad_contract',
'COLUMN', N'CreateManName'
GO

EXEC sp_addextendedproperty
'MS_Description', N'创建人员工编号',
'SCHEMA', N'dbo',
'TABLE', N'ad_contract',
'COLUMN', N'CreateManNo'
GO

EXEC sp_addextendedproperty
'MS_Description', N'编辑时间',
'SCHEMA', N'dbo',
'TABLE', N'ad_contract',
'COLUMN', N'EditTime'
GO

EXEC sp_addextendedproperty
'MS_Description', N'编辑人姓名',
'SCHEMA', N'dbo',
'TABLE', N'ad_contract',
'COLUMN', N'EditManName'
GO

EXEC sp_addextendedproperty
'MS_Description', N'编辑人员工编号',
'SCHEMA', N'dbo',
'TABLE', N'ad_contract',
'COLUMN', N'EditManNo'
GO

EXEC sp_addextendedproperty
'MS_Description', N'合同路径',
'SCHEMA', N'dbo',
'TABLE', N'ad_contract',
'COLUMN', N'ContractUrl'
GO

EXEC sp_addextendedproperty
'MS_Description', N'展位编号',
'SCHEMA', N'dbo',
'TABLE', N'ad_contract',
'COLUMN', N'ZwNo'
GO

EXEC sp_addextendedproperty
'MS_Description', N'保额',
'SCHEMA', N'dbo',
'TABLE', N'ad_contract',
'COLUMN', N'SafeMoney'
GO

EXEC sp_addextendedproperty
'MS_Description', N'合同信息表',
'SCHEMA', N'dbo',
'TABLE', N'ad_contract'
GO


-- ----------------------------
-- Table structure for ad_product_series
-- ----------------------------
IF EXISTS (SELECT * FROM sys.all_objects WHERE object_id = OBJECT_ID(N'[dbo].[ad_product_series]') AND type IN ('U'))
	DROP TABLE [dbo].[ad_product_series]
GO

CREATE TABLE [dbo].[ad_product_series] (
  [Id] int  IDENTITY(1,1) NOT NULL,
  [SeriesName] varchar(100) COLLATE Chinese_PRC_CI_AS  NULL,
  [BrandNo] varchar(50) COLLATE Chinese_PRC_CI_AS  NULL,
  [CategoryId] int  NULL,
  [MoneyRange] varchar(50) COLLATE Chinese_PRC_CI_AS  NULL,
  [ProductMajor] varchar(200) COLLATE Chinese_PRC_CI_AS  NULL,
  [CreateTime] datetime  NULL,
  [CreateManName] varchar(50) COLLATE Chinese_PRC_CI_AS  NULL,
  [CreateManNo] varchar(50) COLLATE Chinese_PRC_CI_AS  NULL,
  [EditTime] datetime  NULL,
  [EditManName] varchar(50) COLLATE Chinese_PRC_CI_AS  NULL,
  [EditManNo] varchar(50) COLLATE Chinese_PRC_CI_AS  NULL,
  [SeriesNo] varchar(50) COLLATE Chinese_PRC_CI_AS  NULL
)
GO

ALTER TABLE [dbo].[ad_product_series] SET (LOCK_ESCALATION = TABLE)
GO

EXEC sp_addextendedproperty
'MS_Description', N'产品系列名称',
'SCHEMA', N'dbo',
'TABLE', N'ad_product_series',
'COLUMN', N'SeriesName'
GO

EXEC sp_addextendedproperty
'MS_Description', N'对应品牌No',
'SCHEMA', N'dbo',
'TABLE', N'ad_product_series',
'COLUMN', N'BrandNo'
GO

EXEC sp_addextendedproperty
'MS_Description', N'产品风格属性',
'SCHEMA', N'dbo',
'TABLE', N'ad_product_series',
'COLUMN', N'CategoryId'
GO

EXEC sp_addextendedproperty
'MS_Description', N'价格区间',
'SCHEMA', N'dbo',
'TABLE', N'ad_product_series',
'COLUMN', N'MoneyRange'
GO

EXEC sp_addextendedproperty
'MS_Description', N'产品主材',
'SCHEMA', N'dbo',
'TABLE', N'ad_product_series',
'COLUMN', N'ProductMajor'
GO

EXEC sp_addextendedproperty
'MS_Description', N'创建时间',
'SCHEMA', N'dbo',
'TABLE', N'ad_product_series',
'COLUMN', N'CreateTime'
GO

EXEC sp_addextendedproperty
'MS_Description', N'创建人姓名',
'SCHEMA', N'dbo',
'TABLE', N'ad_product_series',
'COLUMN', N'CreateManName'
GO

EXEC sp_addextendedproperty
'MS_Description', N'创建人员工编号',
'SCHEMA', N'dbo',
'TABLE', N'ad_product_series',
'COLUMN', N'CreateManNo'
GO

EXEC sp_addextendedproperty
'MS_Description', N'编辑时间',
'SCHEMA', N'dbo',
'TABLE', N'ad_product_series',
'COLUMN', N'EditTime'
GO

EXEC sp_addextendedproperty
'MS_Description', N'编辑人姓名',
'SCHEMA', N'dbo',
'TABLE', N'ad_product_series',
'COLUMN', N'EditManName'
GO

EXEC sp_addextendedproperty
'MS_Description', N'编辑人员工编号',
'SCHEMA', N'dbo',
'TABLE', N'ad_product_series',
'COLUMN', N'EditManNo'
GO

EXEC sp_addextendedproperty
'MS_Description', N'产品系列编号',
'SCHEMA', N'dbo',
'TABLE', N'ad_product_series',
'COLUMN', N'SeriesNo'
GO

EXEC sp_addextendedproperty
'MS_Description', N'产品系列信息表',
'SCHEMA', N'dbo',
'TABLE', N'ad_product_series'
GO


-- ----------------------------
-- Table structure for ad_tenant
-- ----------------------------
IF EXISTS (SELECT * FROM sys.all_objects WHERE object_id = OBJECT_ID(N'[dbo].[ad_tenant]') AND type IN ('U'))
	DROP TABLE [dbo].[ad_tenant]
GO

CREATE TABLE [dbo].[ad_tenant] (
  [Id] int  IDENTITY(1,1) NOT NULL,
  [ShNo] varchar(50) COLLATE Chinese_PRC_CI_AS  NOT NULL,
  [ShName] varchar(50) COLLATE Chinese_PRC_CI_AS  NULL,
  [ShCardNo] varchar(18) COLLATE Chinese_PRC_CI_AS  NULL,
  [ShPhone] varchar(11) COLLATE Chinese_PRC_CI_AS  NULL,
  [ShBeiPhone] varchar(18) COLLATE Chinese_PRC_CI_AS  NULL,
  [ShSex] varchar(10) COLLATE Chinese_PRC_CI_AS  NULL,
  [ShNativePlace] varchar(200) COLLATE Chinese_PRC_CI_AS  NULL,
  [ShPoliticsFace] varchar(50) COLLATE Chinese_PRC_CI_AS  NULL,
  [ShHobby] varchar(200) COLLATE Chinese_PRC_CI_AS  NULL,
  [ShEdu] varchar(50) COLLATE Chinese_PRC_CI_AS  NULL,
  [ShGraduation] varchar(100) COLLATE Chinese_PRC_CI_AS  NULL,
  [ShMajor] varchar(100) COLLATE Chinese_PRC_CI_AS  NULL,
  [FurStartDate] date  NULL,
  [SjzBrands] varchar(max) COLLATE Chinese_PRC_CI_AS  NULL,
  [ZwNum] int  NULL,
  [SocialPosition] varchar(50) COLLATE Chinese_PRC_CI_AS  NULL,
  [SocialHonors] varchar(50) COLLATE Chinese_PRC_CI_AS  NULL,
  [CreateTime] datetime  NULL,
  [CreateManName] varchar(50) COLLATE Chinese_PRC_CI_AS  NULL,
  [CreateManNo] varchar(50) COLLATE Chinese_PRC_CI_AS  NULL,
  [EditTime] datetime  NULL,
  [EditManName] varchar(50) COLLATE Chinese_PRC_CI_AS  NULL,
  [EditManNo] varchar(50) COLLATE Chinese_PRC_CI_AS  NULL,
  [SettleBank] varchar(50) COLLATE Chinese_PRC_CI_AS  NULL,
  [OpenBankName] varchar(50) COLLATE Chinese_PRC_CI_AS  NULL,
  [OpenBankType] varchar(20) COLLATE Chinese_PRC_CI_AS  NULL,
  [SettleBankCardNo] varchar(20) COLLATE Chinese_PRC_CI_AS  NULL,
  [DelStatus] int DEFAULT ((0)) NULL,
  [Province] varchar(255) COLLATE Chinese_PRC_CI_AS  NULL,
  [City] varchar(255) COLLATE Chinese_PRC_CI_AS  NULL,
  [County] varchar(255) COLLATE Chinese_PRC_CI_AS  NULL
)
GO

ALTER TABLE [dbo].[ad_tenant] SET (LOCK_ESCALATION = TABLE)
GO

EXEC sp_addextendedproperty
'MS_Description', N'商户编号',
'SCHEMA', N'dbo',
'TABLE', N'ad_tenant',
'COLUMN', N'ShNo'
GO

EXEC sp_addextendedproperty
'MS_Description', N'商户姓名',
'SCHEMA', N'dbo',
'TABLE', N'ad_tenant',
'COLUMN', N'ShName'
GO

EXEC sp_addextendedproperty
'MS_Description', N'商户身份证号',
'SCHEMA', N'dbo',
'TABLE', N'ad_tenant',
'COLUMN', N'ShCardNo'
GO

EXEC sp_addextendedproperty
'MS_Description', N'商户联系电话',
'SCHEMA', N'dbo',
'TABLE', N'ad_tenant',
'COLUMN', N'ShPhone'
GO

EXEC sp_addextendedproperty
'MS_Description', N'商户备用联系电话',
'SCHEMA', N'dbo',
'TABLE', N'ad_tenant',
'COLUMN', N'ShBeiPhone'
GO

EXEC sp_addextendedproperty
'MS_Description', N'商户性别',
'SCHEMA', N'dbo',
'TABLE', N'ad_tenant',
'COLUMN', N'ShSex'
GO

EXEC sp_addextendedproperty
'MS_Description', N'籍贯',
'SCHEMA', N'dbo',
'TABLE', N'ad_tenant',
'COLUMN', N'ShNativePlace'
GO

EXEC sp_addextendedproperty
'MS_Description', N'政治面貌 党员 民主党派 群众',
'SCHEMA', N'dbo',
'TABLE', N'ad_tenant',
'COLUMN', N'ShPoliticsFace'
GO

EXEC sp_addextendedproperty
'MS_Description', N'爱好特长',
'SCHEMA', N'dbo',
'TABLE', N'ad_tenant',
'COLUMN', N'ShHobby'
GO

EXEC sp_addextendedproperty
'MS_Description', N'学历 研究生以上 本科 大专 大专以下',
'SCHEMA', N'dbo',
'TABLE', N'ad_tenant',
'COLUMN', N'ShEdu'
GO

EXEC sp_addextendedproperty
'MS_Description', N'毕业学校',
'SCHEMA', N'dbo',
'TABLE', N'ad_tenant',
'COLUMN', N'ShGraduation'
GO

EXEC sp_addextendedproperty
'MS_Description', N'所学专业',
'SCHEMA', N'dbo',
'TABLE', N'ad_tenant',
'COLUMN', N'ShMajor'
GO

EXEC sp_addextendedproperty
'MS_Description', N'入家具行业时间',
'SCHEMA', N'dbo',
'TABLE', N'ad_tenant',
'COLUMN', N'FurStartDate'
GO

EXEC sp_addextendedproperty
'MS_Description', N'石家庄经营品牌数量',
'SCHEMA', N'dbo',
'TABLE', N'ad_tenant',
'COLUMN', N'SjzBrands'
GO

EXEC sp_addextendedproperty
'MS_Description', N'展位数量',
'SCHEMA', N'dbo',
'TABLE', N'ad_tenant',
'COLUMN', N'ZwNum'
GO

EXEC sp_addextendedproperty
'MS_Description', N'县级以上社会职务 人大代表 政协委员',
'SCHEMA', N'dbo',
'TABLE', N'ad_tenant',
'COLUMN', N'SocialPosition'
GO

EXEC sp_addextendedproperty
'MS_Description', N'县级以上社会荣誉 劳动模范 拔尖人才',
'SCHEMA', N'dbo',
'TABLE', N'ad_tenant',
'COLUMN', N'SocialHonors'
GO

EXEC sp_addextendedproperty
'MS_Description', N'创建时间',
'SCHEMA', N'dbo',
'TABLE', N'ad_tenant',
'COLUMN', N'CreateTime'
GO

EXEC sp_addextendedproperty
'MS_Description', N'创建人姓名',
'SCHEMA', N'dbo',
'TABLE', N'ad_tenant',
'COLUMN', N'CreateManName'
GO

EXEC sp_addextendedproperty
'MS_Description', N'创建人员工编号',
'SCHEMA', N'dbo',
'TABLE', N'ad_tenant',
'COLUMN', N'CreateManNo'
GO

EXEC sp_addextendedproperty
'MS_Description', N'编辑时间',
'SCHEMA', N'dbo',
'TABLE', N'ad_tenant',
'COLUMN', N'EditTime'
GO

EXEC sp_addextendedproperty
'MS_Description', N'编辑人姓名',
'SCHEMA', N'dbo',
'TABLE', N'ad_tenant',
'COLUMN', N'EditManName'
GO

EXEC sp_addextendedproperty
'MS_Description', N'编辑人员工编号',
'SCHEMA', N'dbo',
'TABLE', N'ad_tenant',
'COLUMN', N'EditManNo'
GO

EXEC sp_addextendedproperty
'MS_Description', N'结款银行',
'SCHEMA', N'dbo',
'TABLE', N'ad_tenant',
'COLUMN', N'SettleBank'
GO

EXEC sp_addextendedproperty
'MS_Description', N'开户行名称',
'SCHEMA', N'dbo',
'TABLE', N'ad_tenant',
'COLUMN', N'OpenBankName'
GO

EXEC sp_addextendedproperty
'MS_Description', N'开户行类型
   建行 工行 农行 中行 河行',
'SCHEMA', N'dbo',
'TABLE', N'ad_tenant',
'COLUMN', N'OpenBankType'
GO

EXEC sp_addextendedproperty
'MS_Description', N'银行卡号',
'SCHEMA', N'dbo',
'TABLE', N'ad_tenant',
'COLUMN', N'SettleBankCardNo'
GO

EXEC sp_addextendedproperty
'MS_Description', N'删除标志 0：未删除 1：已删除',
'SCHEMA', N'dbo',
'TABLE', N'ad_tenant',
'COLUMN', N'DelStatus'
GO

EXEC sp_addextendedproperty
'MS_Description', N'所在省',
'SCHEMA', N'dbo',
'TABLE', N'ad_tenant',
'COLUMN', N'Province'
GO

EXEC sp_addextendedproperty
'MS_Description', N'所在市',
'SCHEMA', N'dbo',
'TABLE', N'ad_tenant',
'COLUMN', N'City'
GO

EXEC sp_addextendedproperty
'MS_Description', N'所在县',
'SCHEMA', N'dbo',
'TABLE', N'ad_tenant',
'COLUMN', N'County'
GO

EXEC sp_addextendedproperty
'MS_Description', N'商户信息表',
'SCHEMA', N'dbo',
'TABLE', N'ad_tenant'
GO


-- ----------------------------
-- Table structure for ad_tenant_job
-- ----------------------------
IF EXISTS (SELECT * FROM sys.all_objects WHERE object_id = OBJECT_ID(N'[dbo].[ad_tenant_job]') AND type IN ('U'))
	DROP TABLE [dbo].[ad_tenant_job]
GO

CREATE TABLE [dbo].[ad_tenant_job] (
  [Id] int  IDENTITY(1,1) NOT NULL,
  [ShNo] varchar(200) COLLATE Chinese_PRC_CI_AS  NULL,
  [ManageYear] date  NULL,
  [BusinessAttr] varchar(50) COLLATE Chinese_PRC_CI_AS  NULL,
  [Company] varchar(100) COLLATE Chinese_PRC_CI_AS  NULL,
  [Duty] varchar(50) COLLATE Chinese_PRC_CI_AS  NULL,
  [Remark] varchar(200) COLLATE Chinese_PRC_CI_AS  NULL,
  [CreateTime] datetime  NULL,
  [CreateManName] varchar(50) COLLATE Chinese_PRC_CI_AS  NULL,
  [CreateManNo] varchar(50) COLLATE Chinese_PRC_CI_AS  NULL,
  [EditTime] datetime  NULL,
  [EditManName] varchar(50) COLLATE Chinese_PRC_CI_AS  NULL,
  [EditManNo] varchar(50) COLLATE Chinese_PRC_CI_AS  NULL
)
GO

ALTER TABLE [dbo].[ad_tenant_job] SET (LOCK_ESCALATION = TABLE)
GO

EXEC sp_addextendedproperty
'MS_Description', N'商户编号',
'SCHEMA', N'dbo',
'TABLE', N'ad_tenant_job',
'COLUMN', N'ShNo'
GO

EXEC sp_addextendedproperty
'MS_Description', N'年度',
'SCHEMA', N'dbo',
'TABLE', N'ad_tenant_job',
'COLUMN', N'ManageYear'
GO

EXEC sp_addextendedproperty
'MS_Description', N'行业',
'SCHEMA', N'dbo',
'TABLE', N'ad_tenant_job',
'COLUMN', N'BusinessAttr'
GO

EXEC sp_addextendedproperty
'MS_Description', N'单位',
'SCHEMA', N'dbo',
'TABLE', N'ad_tenant_job',
'COLUMN', N'Company'
GO

EXEC sp_addextendedproperty
'MS_Description', N'职务',
'SCHEMA', N'dbo',
'TABLE', N'ad_tenant_job',
'COLUMN', N'Duty'
GO

EXEC sp_addextendedproperty
'MS_Description', N'备注',
'SCHEMA', N'dbo',
'TABLE', N'ad_tenant_job',
'COLUMN', N'Remark'
GO

EXEC sp_addextendedproperty
'MS_Description', N'创建时间',
'SCHEMA', N'dbo',
'TABLE', N'ad_tenant_job',
'COLUMN', N'CreateTime'
GO

EXEC sp_addextendedproperty
'MS_Description', N'创建人姓名',
'SCHEMA', N'dbo',
'TABLE', N'ad_tenant_job',
'COLUMN', N'CreateManName'
GO

EXEC sp_addextendedproperty
'MS_Description', N'创建人员工编号',
'SCHEMA', N'dbo',
'TABLE', N'ad_tenant_job',
'COLUMN', N'CreateManNo'
GO

EXEC sp_addextendedproperty
'MS_Description', N'编辑时间',
'SCHEMA', N'dbo',
'TABLE', N'ad_tenant_job',
'COLUMN', N'EditTime'
GO

EXEC sp_addextendedproperty
'MS_Description', N'编辑人姓名',
'SCHEMA', N'dbo',
'TABLE', N'ad_tenant_job',
'COLUMN', N'EditManName'
GO

EXEC sp_addextendedproperty
'MS_Description', N'编辑人员工编号',
'SCHEMA', N'dbo',
'TABLE', N'ad_tenant_job',
'COLUMN', N'EditManNo'
GO

EXEC sp_addextendedproperty
'MS_Description', N'商户工作履历表',
'SCHEMA', N'dbo',
'TABLE', N'ad_tenant_job'
GO


-- ----------------------------
-- Table structure for ad_tenant_relation
-- ----------------------------
IF EXISTS (SELECT * FROM sys.all_objects WHERE object_id = OBJECT_ID(N'[dbo].[ad_tenant_relation]') AND type IN ('U'))
	DROP TABLE [dbo].[ad_tenant_relation]
GO

CREATE TABLE [dbo].[ad_tenant_relation] (
  [Id] int  IDENTITY(1,1) NOT NULL,
  [ShNo] varchar(200) COLLATE Chinese_PRC_CI_AS  NULL,
  [FamName] varchar(50) COLLATE Chinese_PRC_CI_AS  NULL,
  [FamPhone] varchar(11) COLLATE Chinese_PRC_CI_AS  NULL,
  [Relation] varchar(50) COLLATE Chinese_PRC_CI_AS  NULL,
  [JobGoing] varchar(200) COLLATE Chinese_PRC_CI_AS  NULL,
  [CreateTime] datetime  NULL,
  [CreateManName] varchar(50) COLLATE Chinese_PRC_CI_AS  NULL,
  [CreateManNo] varchar(50) COLLATE Chinese_PRC_CI_AS  NULL,
  [EditTime] datetime  NULL,
  [EditManName] varchar(50) COLLATE Chinese_PRC_CI_AS  NULL,
  [EditManNo] varchar(50) COLLATE Chinese_PRC_CI_AS  NULL
)
GO

ALTER TABLE [dbo].[ad_tenant_relation] SET (LOCK_ESCALATION = TABLE)
GO

EXEC sp_addextendedproperty
'MS_Description', N'商户编号',
'SCHEMA', N'dbo',
'TABLE', N'ad_tenant_relation',
'COLUMN', N'ShNo'
GO

EXEC sp_addextendedproperty
'MS_Description', N'家人姓名',
'SCHEMA', N'dbo',
'TABLE', N'ad_tenant_relation',
'COLUMN', N'FamName'
GO

EXEC sp_addextendedproperty
'MS_Description', N'电话',
'SCHEMA', N'dbo',
'TABLE', N'ad_tenant_relation',
'COLUMN', N'FamPhone'
GO

EXEC sp_addextendedproperty
'MS_Description', N'关系',
'SCHEMA', N'dbo',
'TABLE', N'ad_tenant_relation',
'COLUMN', N'Relation'
GO

EXEC sp_addextendedproperty
'MS_Description', N'工作情况',
'SCHEMA', N'dbo',
'TABLE', N'ad_tenant_relation',
'COLUMN', N'JobGoing'
GO

EXEC sp_addextendedproperty
'MS_Description', N'创建时间',
'SCHEMA', N'dbo',
'TABLE', N'ad_tenant_relation',
'COLUMN', N'CreateTime'
GO

EXEC sp_addextendedproperty
'MS_Description', N'创建人姓名',
'SCHEMA', N'dbo',
'TABLE', N'ad_tenant_relation',
'COLUMN', N'CreateManName'
GO

EXEC sp_addextendedproperty
'MS_Description', N'创建人员工编号',
'SCHEMA', N'dbo',
'TABLE', N'ad_tenant_relation',
'COLUMN', N'CreateManNo'
GO

EXEC sp_addextendedproperty
'MS_Description', N'编辑时间',
'SCHEMA', N'dbo',
'TABLE', N'ad_tenant_relation',
'COLUMN', N'EditTime'
GO

EXEC sp_addextendedproperty
'MS_Description', N'编辑人姓名',
'SCHEMA', N'dbo',
'TABLE', N'ad_tenant_relation',
'COLUMN', N'EditManName'
GO

EXEC sp_addextendedproperty
'MS_Description', N'编辑人员工编号',
'SCHEMA', N'dbo',
'TABLE', N'ad_tenant_relation',
'COLUMN', N'EditManNo'
GO

EXEC sp_addextendedproperty
'MS_Description', N'商户家人情况表',
'SCHEMA', N'dbo',
'TABLE', N'ad_tenant_relation'
GO


-- ----------------------------
-- Table structure for ad_tenant_stand
-- ----------------------------
IF EXISTS (SELECT * FROM sys.all_objects WHERE object_id = OBJECT_ID(N'[dbo].[ad_tenant_stand]') AND type IN ('U'))
	DROP TABLE [dbo].[ad_tenant_stand]
GO

CREATE TABLE [dbo].[ad_tenant_stand] (
  [Id] int  IDENTITY(1,1) NOT NULL,
  [ShNo] varchar(50) COLLATE Chinese_PRC_CI_AS  NULL,
  [BrandNo] varchar(50) COLLATE Chinese_PRC_CI_AS  NULL,
  [StoreType] varchar(50) COLLATE Chinese_PRC_CI_AS  NULL,
  [MangeYear] date  NULL,
  [SeriesNo] varchar(50) COLLATE Chinese_PRC_CI_AS  NULL,
  [Floor] varchar(50) COLLATE Chinese_PRC_CI_AS  NULL,
  [ZwArea] decimal(18,2)  NULL,
  [Remard] varchar(200) COLLATE Chinese_PRC_CI_AS  NULL,
  [IsLastMange] int DEFAULT ((0)) NULL,
  [CreateTime] datetime  NULL,
  [CreateManName] varchar(50) COLLATE Chinese_PRC_CI_AS  NULL,
  [CreateManNo] varchar(50) COLLATE Chinese_PRC_CI_AS  NULL,
  [EditTime] datetime  NULL,
  [EditManName] varchar(50) COLLATE Chinese_PRC_CI_AS  NULL,
  [EditManNo] varchar(50) COLLATE Chinese_PRC_CI_AS  NULL,
  [ZwNo] varchar(50) COLLATE Chinese_PRC_CI_AS  NOT NULL,
  [Store] varchar(50) COLLATE Chinese_PRC_CI_AS  NULL,
  [StandStatus] varchar(1) COLLATE Chinese_PRC_CI_AS DEFAULT ((1)) NULL
)
GO

ALTER TABLE [dbo].[ad_tenant_stand] SET (LOCK_ESCALATION = TABLE)
GO

EXEC sp_addextendedproperty
'MS_Description', N'商户No',
'SCHEMA', N'dbo',
'TABLE', N'ad_tenant_stand',
'COLUMN', N'ShNo'
GO

EXEC sp_addextendedproperty
'MS_Description', N'经营品牌编号',
'SCHEMA', N'dbo',
'TABLE', N'ad_tenant_stand',
'COLUMN', N'BrandNo'
GO

EXEC sp_addextendedproperty
'MS_Description', N'卖场',
'SCHEMA', N'dbo',
'TABLE', N'ad_tenant_stand',
'COLUMN', N'StoreType'
GO

EXEC sp_addextendedproperty
'MS_Description', N'年度',
'SCHEMA', N'dbo',
'TABLE', N'ad_tenant_stand',
'COLUMN', N'MangeYear'
GO

EXEC sp_addextendedproperty
'MS_Description', N'系列',
'SCHEMA', N'dbo',
'TABLE', N'ad_tenant_stand',
'COLUMN', N'SeriesNo'
GO

EXEC sp_addextendedproperty
'MS_Description', N'楼层',
'SCHEMA', N'dbo',
'TABLE', N'ad_tenant_stand',
'COLUMN', N'Floor'
GO

EXEC sp_addextendedproperty
'MS_Description', N'面积',
'SCHEMA', N'dbo',
'TABLE', N'ad_tenant_stand',
'COLUMN', N'ZwArea'
GO

EXEC sp_addextendedproperty
'MS_Description', N'备注',
'SCHEMA', N'dbo',
'TABLE', N'ad_tenant_stand',
'COLUMN', N'Remard'
GO

EXEC sp_addextendedproperty
'MS_Description', N'是否为曾代理 0：否 1：是',
'SCHEMA', N'dbo',
'TABLE', N'ad_tenant_stand',
'COLUMN', N'IsLastMange'
GO

EXEC sp_addextendedproperty
'MS_Description', N'创建时间',
'SCHEMA', N'dbo',
'TABLE', N'ad_tenant_stand',
'COLUMN', N'CreateTime'
GO

EXEC sp_addextendedproperty
'MS_Description', N'创建人姓名',
'SCHEMA', N'dbo',
'TABLE', N'ad_tenant_stand',
'COLUMN', N'CreateManName'
GO

EXEC sp_addextendedproperty
'MS_Description', N'创建人员工编号',
'SCHEMA', N'dbo',
'TABLE', N'ad_tenant_stand',
'COLUMN', N'CreateManNo'
GO

EXEC sp_addextendedproperty
'MS_Description', N'编辑时间',
'SCHEMA', N'dbo',
'TABLE', N'ad_tenant_stand',
'COLUMN', N'EditTime'
GO

EXEC sp_addextendedproperty
'MS_Description', N'编辑人姓名',
'SCHEMA', N'dbo',
'TABLE', N'ad_tenant_stand',
'COLUMN', N'EditManName'
GO

EXEC sp_addextendedproperty
'MS_Description', N'编辑人员工编号',
'SCHEMA', N'dbo',
'TABLE', N'ad_tenant_stand',
'COLUMN', N'EditManNo'
GO

EXEC sp_addextendedproperty
'MS_Description', N'展位编号',
'SCHEMA', N'dbo',
'TABLE', N'ad_tenant_stand',
'COLUMN', N'ZwNo'
GO

EXEC sp_addextendedproperty
'MS_Description', N'店面',
'SCHEMA', N'dbo',
'TABLE', N'ad_tenant_stand',
'COLUMN', N'Store'
GO

EXEC sp_addextendedproperty
'MS_Description', N'展位状态 1：在展 2：报撤 3： 撤展',
'SCHEMA', N'dbo',
'TABLE', N'ad_tenant_stand',
'COLUMN', N'StandStatus'
GO

EXEC sp_addextendedproperty
'MS_Description', N'商户展位表',
'SCHEMA', N'dbo',
'TABLE', N'ad_tenant_stand'
GO


-- ----------------------------
-- Table structure for as_item
-- ----------------------------
IF EXISTS (SELECT * FROM sys.all_objects WHERE object_id = OBJECT_ID(N'[dbo].[as_item]') AND type IN ('U'))
	DROP TABLE [dbo].[as_item]
GO

CREATE TABLE [dbo].[as_item] (
  [Id] int  IDENTITY(1,1) NOT NULL,
  [Type] int  NULL,
  [Name] varchar(50) COLLATE Chinese_PRC_CI_AS  NULL
)
GO

ALTER TABLE [dbo].[as_item] SET (LOCK_ESCALATION = TABLE)
GO

EXEC sp_addextendedproperty
'MS_Description', N'1、行业属性 2、银行',
'SCHEMA', N'dbo',
'TABLE', N'as_item',
'COLUMN', N'Type'
GO

EXEC sp_addextendedproperty
'MS_Description', N'字段名称',
'SCHEMA', N'dbo',
'TABLE', N'as_item',
'COLUMN', N'Name'
GO

EXEC sp_addextendedproperty
'MS_Description', N'杂项字典表',
'SCHEMA', N'dbo',
'TABLE', N'as_item'
GO


-- ----------------------------
-- Table structure for as_store_item
-- ----------------------------
IF EXISTS (SELECT * FROM sys.all_objects WHERE object_id = OBJECT_ID(N'[dbo].[as_store_item]') AND type IN ('U'))
	DROP TABLE [dbo].[as_store_item]
GO

CREATE TABLE [dbo].[as_store_item] (
  [Id] int  NOT NULL,
  [Name] varchar(50) COLLATE Chinese_PRC_CI_AS  NULL,
  [ParentId] int  NULL,
  [Level] int  NULL
)
GO

ALTER TABLE [dbo].[as_store_item] SET (LOCK_ESCALATION = TABLE)
GO

EXEC sp_addextendedproperty
'MS_Description', N'字段名称',
'SCHEMA', N'dbo',
'TABLE', N'as_store_item',
'COLUMN', N'Name'
GO

EXEC sp_addextendedproperty
'MS_Description', N'父级Id',
'SCHEMA', N'dbo',
'TABLE', N'as_store_item',
'COLUMN', N'ParentId'
GO

EXEC sp_addextendedproperty
'MS_Description', N'层级',
'SCHEMA', N'dbo',
'TABLE', N'as_store_item',
'COLUMN', N'Level'
GO

EXEC sp_addextendedproperty
'MS_Description', N'店面分类字典表',
'SCHEMA', N'dbo',
'TABLE', N'as_store_item'
GO


-- ----------------------------
-- Function structure for Fun_SplitStr
-- ----------------------------
IF EXISTS (SELECT * FROM sys.all_objects WHERE object_id = OBJECT_ID(N'[dbo].[Fun_SplitStr]') AND type IN ('FN', 'FS', 'FT', 'IF', 'TF'))
	DROP FUNCTION[dbo].[Fun_SplitStr]
GO

CREATE FUNCTION [dbo].[Fun_SplitStr]
(
    @originalStr      VARCHAR(8000), --要分割的字符串
    @split varchar(100)  --分隔符号
)
RETURNS @temp TABLE(Result VARCHAR(100))
AS
BEGIN
    DECLARE @result AS VARCHAR(100);   --定义变量用于接收单个结果 
    
    SET @originalStr = @originalStr + @split ;  
      
    WHILE (@originalStr <> '')
    BEGIN
        SET @result = LEFT(@originalStr, CHARINDEX(@split, @originalStr, 1) -1) ;  
        
        INSERT @temp VALUES(@result) ;  
        
        --STUFF()函数用于删除指定长度的字符，并可以在指定的起点处插入另一组字符。
        SET @originalStr = STUFF(@originalStr, 1, CHARINDEX(@split, @originalStr, 1), '');
    END 
    RETURN
END
GO


-- ----------------------------
-- Function structure for fn_GetSeriesNo
-- ----------------------------
IF EXISTS (SELECT * FROM sys.all_objects WHERE object_id = OBJECT_ID(N'[dbo].[fn_GetSeriesNo]') AND type IN ('FN', 'FS', 'FT', 'IF', 'TF'))
	DROP FUNCTION[dbo].[fn_GetSeriesNo]
GO

CREATE FUNCTION [dbo].[fn_GetSeriesNo]()
returns varchar(50)
as
begin
declare @SeriesNo varchar(50)
declare @monthNum INT
DECLARE @totalNum INT
select @totalNum = count(SeriesNo) from ad_product_series
select @monthNum = count(SeriesNo) from ad_product_series where DATEDIFF(month,createTime,GETDATE())=0
select @SeriesNo ='XL'+'-'+CONVERT(VARCHAR(4),year(getdate()))+right('00'+CONVERT(VARCHAR(50),month(getdate())),2)+'-'+right('0000'+CONVERT(VARCHAR(50),@monthNum+1),4)+'-'+right('000000'+CONVERT(VARCHAR(50),@totalNum+1),6)
return @SeriesNo
end
GO


-- ----------------------------
-- Function structure for fn_GetBrandNo
-- ----------------------------
IF EXISTS (SELECT * FROM sys.all_objects WHERE object_id = OBJECT_ID(N'[dbo].[fn_GetBrandNo]') AND type IN ('FN', 'FS', 'FT', 'IF', 'TF'))
	DROP FUNCTION[dbo].[fn_GetBrandNo]
GO

CREATE FUNCTION [dbo].[fn_GetBrandNo]()
returns varchar(50)
as
begin
declare @BrandNo varchar(50)
declare @monthNum INT
DECLARE @totalNum INT
select @totalNum = count(BrandNo) from ad_brand
select @monthNum = count(BrandNo) from ad_brand where DATEDIFF(month,createTime,GETDATE())=0
select @BrandNo ='PP'+'-'+CONVERT(VARCHAR(4),year(getdate()))+right('00'+CONVERT(VARCHAR(50),month(getdate())),2)+'-'+right('0000'+CONVERT(VARCHAR(50),@monthNum+1),4)+'-'+right('000000'+CONVERT(VARCHAR(50),@totalNum+1),6)
return @BrandNo
end
GO


-- ----------------------------
-- Function structure for Fun_GetStrArrayStrOfIndex
-- ----------------------------
IF EXISTS (SELECT * FROM sys.all_objects WHERE object_id = OBJECT_ID(N'[dbo].[Fun_GetStrArrayStrOfIndex]') AND type IN ('FN', 'FS', 'FT', 'IF', 'TF'))
	DROP FUNCTION[dbo].[Fun_GetStrArrayStrOfIndex]
GO

CREATE FUNCTION [dbo].[Fun_GetStrArrayStrOfIndex]
(
    @originalStr     VARCHAR(1024),    --要分割的字符串
    @split           VARCHAR(10),    --分隔符号
    @index           INT --取第几个元素
)
RETURNS VARCHAR(1024)
AS
BEGIN
    DECLARE @location INT; --定义第一次出现分隔符号的位置
    DECLARE @start INT;--定义开始位置
    DECLARE @next INT; --定义下一个位置
    DECLARE @seed INT; --定义分割符号的长度
    
    SET @originalStr = LTRIM(RTRIM(@originalStr));--去除字符串左右2侧空格
    SET @start = 1;
    SET @next = 1;
    SET @seed = LEN(@split);
    
    SET @location = CHARINDEX(@split, @originalStr);--第一次出现分隔符号的位置
    
    WHILE @location <> 0
          AND @index > @next
    BEGIN
        SET @start = @location + @seed;
        SET @location = CHARINDEX(@split, @originalStr, @start);
        SET @next = @next + 1;
    END
    
    IF @location = 0
        BEGIN
            SELECT @location = LEN(@originalStr) + 1;
        END

    --存在两种情况：
    --1、字符串不存在分隔符号。
    --2、字符串中存在分隔符号，跳出while循环后，@location为0，那默认为字符串后边有一个分隔符号。
    
    RETURN SUBSTRING(@originalStr, @start, @location -@start);
END
GO


-- ----------------------------
-- Procedure structure for proc_Series_Test
-- ----------------------------
IF EXISTS (SELECT * FROM sys.all_objects WHERE object_id = OBJECT_ID(N'[dbo].[proc_Series_Test]') AND type IN ('P', 'PC', 'RF', 'X'))
	DROP PROCEDURE[dbo].[proc_Series_Test]
GO

CREATE PROCEDURE [dbo].[proc_Series_Test](
    @SeriesNameList VARCHAR(1024)    --要分割的字符串   
)

 AS
begin   
declare @SeriesNoList varchar(200)=''
    declare @SeriesName  VARCHAR(200)    --定义变量用于接收单个结果 
    declare @split varchar(10)  = ','
		declare @BrandNo varchar(10)  = 'PP-1'
		declare @SeriesNo varchar(100)
		declare @error varchar(100)

    WHILE (@SeriesNameList <> '')
     BEGIN
        SET @SeriesName = LEFT(@SeriesNameList, CHARINDEX(@split, @SeriesNameList, 1) -1) ;  
        
        if exists (select 1 from ad_product_series where SeriesName=@SeriesName and BrandNo=@BrandNo) 
					begin
							select @SeriesNo = SeriesNo from ad_product_series where SeriesName=@SeriesName and BrandNo=@BrandNo
					end 
					else 
					begin 
							set @SeriesNo = dbo.fn_GetSeriesNo()
							insert into ad_product_series(SeriesName,BrandNo,SeriesNo) values(@SeriesName,@BrandNo,@SeriesNo)
							set @error+=@@ERROR --记录有可能产生的错误号 				
				end
        
				set @SeriesNoList = @SeriesNoList + @SeriesNo + ','      
        --STUFF()函数用于删除指定长度的字符，并可以在指定的起点处插入另一组字符。
        SET @SeriesNameList = STUFF(@SeriesNameList, 1, CHARINDEX(@split, @SeriesNameList, 1), '');
     END  
   update ad_contract  set SeriesNo = @SeriesNoList
end
GO


-- ----------------------------
-- Function structure for Fun_GetStrArrayLength
-- ----------------------------
IF EXISTS (SELECT * FROM sys.all_objects WHERE object_id = OBJECT_ID(N'[dbo].[Fun_GetStrArrayLength]') AND type IN ('FN', 'FS', 'FT', 'IF', 'TF'))
	DROP FUNCTION[dbo].[Fun_GetStrArrayLength]
GO

CREATE FUNCTION [dbo].[Fun_GetStrArrayLength]
(
    @originalStr VARCHAR(1024),    --要分割的字符串
    @split       VARCHAR(10) --分隔符号
)
RETURNS INT
AS
BEGIN
    DECLARE @location INT;--定义起始位置
    DECLARE @start INT; --定义从第几个开始
    DECLARE @length INT;--定义变量，用于接收计算元素的个数
    
    SET @originalStr = LTRIM(RTRIM(@originalStr)); --去除字符串左右两侧的空格
    
    SET @location = CHARINDEX(@split, @originalStr); --分割符号在字符串中第一次出现的位置（索引从1开始计数）
    
    SET @length = 1;
    
    WHILE @location <> 0
    BEGIN
        SET @start = @location + 1;
        SET @location = CHARINDEX(@split, @originalStr, @start);
        SET @length = @length + 1;
    END
    RETURN @length;
END
GO


-- ----------------------------
-- Function structure for fn_GetShNo
-- ----------------------------
IF EXISTS (SELECT * FROM sys.all_objects WHERE object_id = OBJECT_ID(N'[dbo].[fn_GetShNo]') AND type IN ('FN', 'FS', 'FT', 'IF', 'TF'))
	DROP FUNCTION[dbo].[fn_GetShNo]
GO

CREATE FUNCTION [dbo].[fn_GetShNo]()
returns varchar(50)
as
begin
declare @shNo varchar(50)
declare @monthNum INT
DECLARE @totalNum INT
select @totalNum = count(shNo) from ad_tenant
select @monthNum = count(shNo) from ad_tenant where DATEDIFF(month,createTime,GETDATE())=0
select @shNo ='SH'+'-'+CONVERT(VARCHAR(4),year(getdate()))+right('00'+CONVERT(VARCHAR(50),month(getdate())),2)+'-'+right('0000'+CONVERT(VARCHAR(50),@monthNum+1),4)+'-'+right('000000'+CONVERT(VARCHAR(50),@totalNum+1),6)
return @shNo
end
GO


-- ----------------------------
-- Function structure for fn_GetContractNo
-- ----------------------------
IF EXISTS (SELECT * FROM sys.all_objects WHERE object_id = OBJECT_ID(N'[dbo].[fn_GetContractNo]') AND type IN ('FN', 'FS', 'FT', 'IF', 'TF'))
	DROP FUNCTION[dbo].[fn_GetContractNo]
GO

CREATE FUNCTION [dbo].[fn_GetContractNo]()
returns varchar(50)
as
begin
declare @ContractNo varchar(50)
declare @monthNum INT
DECLARE @totalNum INT
select @totalNum = count(ContractNo) from ad_contract
select @monthNum = count(ContractNo) from ad_contract where DATEDIFF(month,createTime,GETDATE())=0
select @ContractNo ='HT'+'-'+CONVERT(VARCHAR(4),year(getdate()))+right('00'+CONVERT(VARCHAR(50),month(getdate())),2)+'-'+right('0000'+CONVERT(VARCHAR(50),@monthNum+1),4)+'-'+right('000000'+CONVERT(VARCHAR(50),@totalNum+1),6)
return @ContractNo
end
GO


-- ----------------------------
-- Function structure for fn_GetZwNo
-- ----------------------------
IF EXISTS (SELECT * FROM sys.all_objects WHERE object_id = OBJECT_ID(N'[dbo].[fn_GetZwNo]') AND type IN ('FN', 'FS', 'FT', 'IF', 'TF'))
	DROP FUNCTION[dbo].[fn_GetZwNo]
GO

CREATE FUNCTION [dbo].[fn_GetZwNo]()
returns varchar(50)
as
begin
declare @ZwNo varchar(50)
declare @monthNum INT
DECLARE @totalNum INT
select @totalNum = count(ZwNo) from ad_tenant_stand
select @monthNum = count(ZwNo) from ad_tenant_stand where DATEDIFF(month,createTime,GETDATE())=0
select @ZwNo ='ZW'+'-'+CONVERT(VARCHAR(4),year(getdate()))+right('00'+CONVERT(VARCHAR(50),month(getdate())),2)+'-'+right('0000'+CONVERT(VARCHAR(50),@monthNum+1),4)+'-'+right('000000'+CONVERT(VARCHAR(50),@totalNum+1),6)
return @ZwNo
end
GO


-- ----------------------------
-- Procedure structure for proc_AddContract
-- ----------------------------
IF EXISTS (SELECT * FROM sys.all_objects WHERE object_id = OBJECT_ID(N'[dbo].[proc_AddContract]') AND type IN ('P', 'PC', 'RF', 'X'))
	DROP PROCEDURE[dbo].[proc_AddContract]
GO

CREATE PROCEDURE [dbo].[proc_AddContract]
@Store varchar(50),
@Floor varchar(50),
@BusinessAttr varchar(50),
@BusinessCategory varchar(50),
@BoothNo varchar(200),
@GtRatio decimal(18,2) = 0,
@RentalArea decimal(18,2) = 0,
@RentalStartDate date,
@RentalEndDate date,
@RentalMoney decimal(18,2) = 0,
@PropertyMoney decimal(18,2) = 0,
@SettleBank varchar(50),
@OpenBankName varchar(50),
@OpenBankType varchar(20),
@SettleOpenBank varchar(200),
@SettleBankCardNo varchar(20),
@ContractMoney decimal(18,2) = 0,
@QualityMoney decimal(18,2) = 0,
@YearSafeMoney decimal(18,2) = 0,
@YearQualityTestMoney decimal(18,2) = 0 ,
@SalesPickUp decimal(18,2) = 0,
@BrandProMoney decimal(18,2) = 0,
@FranchiserStore varchar(50),
@FranchiserBm varchar(50),
@FranchiserUserNo varchar(50),
@FranchiserName varchar(50),
@CreateManName varchar(50),
@CreateManNo varchar(50),
@ShName varchar(50),
@BrandName varchar(50),
@ShCardNo varchar(18),
@ShPhone varchar(11),
@SeriesNameList varchar(200) --可能是多个系列名称；多个系列名称用, 用英文逗号隔开；最后一个名称也有逗号
AS
  declare @error int = 0 --事务中操作的错误记录
  declare @ContractNo varchar(50) --合同编号
  declare @ShNo varchar(50) --商户编号
  declare @BrandNo varchar(50) --品牌编号
  declare @SeriesNo varchar(50) --系列编号
  declare @SeriesNoList varchar(200) = '' --系列编号List
  declare @ZwNo varchar(50) --展位编号
  --开启事务
  begin transaction
   --获取当前商户信息的编号（如果商户身份证号存在，返回商户编号；商户身份证号不存在，添加并返回商户编号）   
    if exists (select 1 from ad_tenant where ShCardNo=@ShCardNo) --说明记录存在，直接返回商户编号
      begin
       select @ShNo = ShNo from ad_tenant where ShCardNo=@ShCardNo
    end 
    else --这个商户不存在，那么就应该添加新的存在信息
      begin   
        set @ShNo = dbo.fn_GetShNo()
        insert into ad_tenant(ShNo,ShName,ShCardNo,ShPhone) values(@ShNo,@ShName,@ShCardNo,@ShPhone)
				set @error+=@@ERROR --记录有可能产生的错误号 
    end

 --获取当前品牌信息的编号（如果品牌名称存在，返回品牌编号；品牌名称不存在，添加并返回品牌编号）  
    if exists (select 1 from ad_brand where brandName=@brandName) 
     begin
       select @BrandNo = BrandNo from ad_brand where brandName=@brandName
    end 
    else 
      begin 
				set @BrandNo = dbo.fn_GetBrandNo()
        insert into ad_brand(BrandNo,BrandName) values(@BrandNo,@BrandName)
				set @error+=@@ERROR --记录有可能产生的错误号 				
      end

    --多个系列名称用,隔开
    declare @SeriesName  VARCHAR(50)   --定义变量用于接收单个结果 
    declare @split varchar(10)  = ','
		--处理多个产品系列   
    WHILE (@SeriesNameList <> '')
     BEGIN
        SET @SeriesName = LEFT(@SeriesNameList, CHARINDEX(@split, @SeriesNameList, 1) -1) ;  
         --获取当前品牌系列信息的编号（如果品牌下产品系列名称存在，返回系列编号；系列名称不存在，添加并返回系列编号）  
        if exists (select 1 from ad_product_series where SeriesName=@SeriesName and BrandNo=@BrandNo) 
					begin
							select @SeriesNo = SeriesNo from ad_product_series where SeriesName=@SeriesName and BrandNo=@BrandNo
					end 
					else 
					begin 
							set @SeriesNo = dbo.fn_GetSeriesNo()
							insert into ad_product_series(SeriesName,BrandNo,SeriesNo) values(@SeriesName,@BrandNo,@SeriesNo)
							set @error+=@@ERROR --记录有可能产生的错误号 				
				end
        
				set @SeriesNoList = @SeriesNoList + @SeriesNo + ','        
        --STUFF()函数用于删除指定长度的字符，并可以在指定的起点处插入另一组字符。
        SET @SeriesNameList = STUFF(@SeriesNameList, 1, CHARINDEX(@split, @SeriesNameList, 1), '');
     END    

    --实现合同信息的添加
		set @ContractNo = dbo.fn_GetContractNo()
    insert into [ad_contract](
          [ContractNo],[Store],[Floor],[BrandNo],[BusinessAttr],[BusinessCategory],[SeriesNo],[ShNo],[BoothNo],[GtRatio],[RentalArea],[RentalStartDate],
          [RentalEndDate],[RentalMoney],[PropertyMoney],[SettleBank],[OpenBankName],[OpenBankType],[SettleOpenBank],[SettleBankCardNo],[ContractMoney],[QualityMoney],[YearSafeMoney],
          [YearQualityTestMoney],[SalesPickUp],[BrandProMoney],[FranchiserStore],[FranchiserBm],[FranchiserUserNo],[FranchiserName],[CreateTime],[CreateManName],[CreateManNo])
    VALUES(
					@ContractNo,@Store,@Floor,@BrandNo,@BusinessAttr,@BusinessCategory,@SeriesNoList,@ShNo,@BoothNo,@GtRatio,@RentalArea,@RentalStartDate,
					@RentalEndDate,@RentalMoney,@PropertyMoney,@SettleBank,@OpenBankName,@OpenBankType,@SettleOpenBank,@SettleBankCardNo,@ContractMoney,@QualityMoney,@YearSafeMoney,
          @YearQualityTestMoney,@SalesPickUp,@BrandProMoney,@FranchiserStore,@FranchiserBm,@FranchiserUserNo,@FranchiserName,getdate(),@CreateManName,@CreateManNo)
    set @error+=@@ERROR --记录有可能产生的错误号 

    --实现展位信息的添加
    set @ZwNo=dbo.fn_GetZwNo()
    insert into [ad_tenant_stand]([ShNo],[BrandNo],[StoreType],[MangeYear],[SeriesNo],[Floor],[ZwArea] ,[CreateTime] ,[CreateManName],[CreateManNo],[ZwNo] ,[Store])
    VALUES(@ShNo,@BrandNo,'东明',getdate(),@SeriesNoList,@Floor,@RentalArea,getdate(),@CreateManName,@CreateManNo,@ZwNo,@Store)
		set @error+=@@ERROR --记录有可能产生的错误号            

--判断事务的提交或者回滚
if(@error<>0)
  begin
    rollback transaction
    return -1 --设置操作结果错误标识
  end
else
  begin
    commit transaction
    select @ContractNo as contractno
    return 0 --操作成功返回合同编号
  end
GO


-- ----------------------------
-- Primary Key structure for table ad_booth
-- ----------------------------
ALTER TABLE [dbo].[ad_booth] ADD CONSTRAINT [PK_AD_BOOTH] PRIMARY KEY CLUSTERED ([Id])
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)  
ON [PRIMARY]
GO


-- ----------------------------
-- Uniques structure for table ad_brand
-- ----------------------------
ALTER TABLE [dbo].[ad_brand] ADD CONSTRAINT [brandname] UNIQUE NONCLUSTERED ([BrandName] ASC)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)  
ON [PRIMARY]
GO


-- ----------------------------
-- Primary Key structure for table ad_brand
-- ----------------------------
ALTER TABLE [dbo].[ad_brand] ADD CONSTRAINT [PK_AD_BRAND] PRIMARY KEY CLUSTERED ([Id], [BrandNo])
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)  
ON [PRIMARY]
GO


-- ----------------------------
-- Primary Key structure for table ad_brand_pic
-- ----------------------------
ALTER TABLE [dbo].[ad_brand_pic] ADD CONSTRAINT [PK__ad_brand__3214EC073A81B327] PRIMARY KEY CLUSTERED ([Id])
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)  
ON [PRIMARY]
GO


-- ----------------------------
-- Primary Key structure for table ad_category_style
-- ----------------------------
ALTER TABLE [dbo].[ad_category_style] ADD CONSTRAINT [PK_AD_CATEGORY_STYLE] PRIMARY KEY CLUSTERED ([Id])
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)  
ON [PRIMARY]
GO


-- ----------------------------
-- Primary Key structure for table ad_contract
-- ----------------------------
ALTER TABLE [dbo].[ad_contract] ADD CONSTRAINT [PK_AD_CONTRACT] PRIMARY KEY CLUSTERED ([Id], [ContractNo])
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)  
ON [PRIMARY]
GO


-- ----------------------------
-- Primary Key structure for table ad_product_series
-- ----------------------------
ALTER TABLE [dbo].[ad_product_series] ADD CONSTRAINT [PK_AD_PRODUCT_SERIES] PRIMARY KEY CLUSTERED ([Id])
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)  
ON [PRIMARY]
GO


-- ----------------------------
-- Uniques structure for table ad_tenant
-- ----------------------------
ALTER TABLE [dbo].[ad_tenant] ADD CONSTRAINT [shcardno] UNIQUE NONCLUSTERED ([ShCardNo] ASC)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)  
ON [PRIMARY]
GO


-- ----------------------------
-- Primary Key structure for table ad_tenant
-- ----------------------------
ALTER TABLE [dbo].[ad_tenant] ADD CONSTRAINT [PK_AD_TENANT] PRIMARY KEY CLUSTERED ([Id], [ShNo])
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)  
ON [PRIMARY]
GO


-- ----------------------------
-- Primary Key structure for table ad_tenant_job
-- ----------------------------
ALTER TABLE [dbo].[ad_tenant_job] ADD CONSTRAINT [PK_AD_TENANT_JOB] PRIMARY KEY CLUSTERED ([Id])
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)  
ON [PRIMARY]
GO


-- ----------------------------
-- Primary Key structure for table ad_tenant_relation
-- ----------------------------
ALTER TABLE [dbo].[ad_tenant_relation] ADD CONSTRAINT [PK_AD_TENANT_RELATION] PRIMARY KEY CLUSTERED ([Id])
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)  
ON [PRIMARY]
GO


-- ----------------------------
-- Primary Key structure for table ad_tenant_stand
-- ----------------------------
ALTER TABLE [dbo].[ad_tenant_stand] ADD CONSTRAINT [PK_AD_TENANT_STAND] PRIMARY KEY CLUSTERED ([Id], [ZwNo])
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)  
ON [PRIMARY]
GO


-- ----------------------------
-- Primary Key structure for table as_item
-- ----------------------------
ALTER TABLE [dbo].[as_item] ADD CONSTRAINT [PK__as_item__3214EC072C3393D0] PRIMARY KEY CLUSTERED ([Id])
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)  
ON [PRIMARY]
GO


-- ----------------------------
-- Primary Key structure for table as_store_item
-- ----------------------------
ALTER TABLE [dbo].[as_store_item] ADD CONSTRAINT [PK__as_store__3214EC07267ABA7A] PRIMARY KEY CLUSTERED ([Id])
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)  
ON [PRIMARY]
GO

